Category: SQL Server development
SQL Server versions: 2005
Level: Advanced

SQL Server 2005 introduced support for .Net in SQL Server. While this is a very powerful feature, it does come with a high cost.

The CLR support allows writing stored procedures, functions, triggers using programming languages like C#. It allows creating complicated user defined types, and so forth. But while these new constructs can improve query performance, they can make the lives of developers rather difficult. The problem is with updating the new CLR objects.

SQL Server 2005 stores the CLR assemblies in the database itself. Therefore stored procedures and data types that depend on a CLR assembly do not depend on anything else but the database, and are persisted completely in a database backup. Restoring such a backup on a different server will make the CLR objects available in the restored database.

But, like stored procedures need to be changed sometimes, CLR object code is evolving too. This means that the assembly containing CLR objects needs to be updated in a database. TSQL contains an “ALTER ASSEMBLY” command to replace a CLR assembly with a new one. Visual Studio 2005 deploys CLR objects exactly this way. However this statement has some restrictions. One of the most important is that method signatures must remain the same. If this is not the case, and a single method signature has changed, the ALTER ASSEMBLY statement will fail. So how can the assembly be upgraded in this case? Simple. One needs to drop all the CLR objects (stored procedures, functions, DDL and DML triggers), drop the assembly, create the assembly using the new DLL, and recreate all the CLR objects. Sounds tedious, but this is still not the end of the story. CLR assemblies may contain user defined types. Rebuilding (i.e. dropping and creating) an assembly requires that such user defined types are dropped before the assembly is dropped. But this is not possible if there are tables that use the user defined data type.

So, would dropping the tables that use the CLR user defined types, then dropping the user defined type together with all the relevant CLR stored procedures and other objects, then the assembly be the right way to start an assembly upgrade? Well, if the data in our tables is not important, then the answer is yes. This is rarely the case though.

What SQL Compare 5.x does is that it first analyzes the CLR assemblies in the two databases that are compared. It intelligently decides whether a simple ALTER ASSEMBLY statement can be used to modify the assembly, or whether the assembly needs to be rebuilt. In the latter case SQL Compare unbinds the data from tables that depend on a CLR user defined type by migrating the data into a set of temporary tables. These temporary tables use the string representation of the user defined type. Having done this all the relevant CLR objects are dropped, and finally the CLR assembly can be rebuilt. Following this all the above CLR objects are rebuilt based on the new CLR assembly, and the data in the temporary tables is converted back to use the new CLR user defined type. By default we assume that the string representation of a CLR UDT is the same in the two versions. This assumption holds in the majority of the cases. However, in the cases where the string representation changes between the different CLR assembly versions, an extra conversion function can be added manually to the migration script.

The nice thing is that all of this is done automatically in SQL Compare 5.x, thus upgrading to a new version of CLR assembly does no longer require potentially dropping all dependent CLR objects by hand and risking data loss.

Andras